package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;
import com.cn.wanxi.util.JDBCSingtence;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserImpl implements UserDao {

    @Override
    public int add(UserModel userModel) {
        String sql = "INSERT INTO `user` " +
                "(`username`, `address`, `create_time`, " +
                "`email`, `enable`, `password`, " +
                "`phone`, `sex`, `update_time`, " +
                "`hobby`, `birthday`, `remark`) VALUES " +
                "(?, ?, now(), " +
                "?, ?, ?," +
                " ?,?, sysdate(), " +
                "?, ?, '');\n";
        List<Object> list = new ArrayList<>();
        list.add(userModel.getUsername());
        list.add(userModel.getAddress());
        list.add(userModel.getEmail());
        list.add(userModel.getEnable());
        list.add(userModel.getPassword());
        list.add(userModel.getPhone());
        list.add(userModel.getSex());
        list.add(userModel.getHobby());
        list.add(userModel.getBirthday());
        return JDBC.getJdbc().update(sql, list);
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "UPDATE `user` SET \n" +
                "`username` = '1', \n" +
                "`address` = '1', \n" +
                "`create_time` =now(),\n" +
                " `email` = '1',\n" +
                " `enable` = '1', \n" +
                " `phone` = '1',\n" +
                " `sex` = '1', \n" +
                " `update_time` =sysdate(),\n" +
                " `hobby` = '1', \n" +
                " `birthday` = '1', \n" +
                " `remark` = '1'\n";
        sql += " where id=" + userModel.getId();
        return JDBC.getJdbc().update(sql);
    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
        return JDBC.getJdbc().update(sql);
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select *,date_format( birthday,'%Y-%m-%d') temp from user where id=" + userModel.getId();
        ResultSet resultSet = JDBC.getJdbc().query(sql);
        List<UserModel> list = getList(resultSet);
        return list.size() > 0 ? list.get(0) : null;
    }

    /**
     * 以后不要有*号
     * 需要什么，给什么
     *
     * @param userModel
     * @return
     */
    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = " select *,date_format( birthday,'%Y-%m-%d') temp from user ";
        sql += getWhereSql(userModel);
        sql += " order by id desc ";
        int start = (userModel.getPage() - 1) * userModel.getLimit();
        sql += " limit " + start + "," + userModel.getLimit();
        return getList(JDBC.getJdbc().query(sql));
    }

    private List<UserModel> getList(ResultSet resultSet) {
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {

                UserModel model = new UserModel();
                model.setUsername(resultSet.getString("username"));
                model.setSex(resultSet.getString("sex"));
                model.setPhone(resultSet.getString("phone"));
                model.setEmail(resultSet.getString("email"));
                model.setAddress(resultSet.getString("address"));
                model.setHobby(resultSet.getString("hobby"));
//                Date birthday=resultSet.getDate("birthday");
//                SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
//
//                model.setBirthday(simpleDateFormat.format(birthday));
                model.setBirthday(resultSet.getString("temp"));
                model.setPhone(resultSet.getString("phone"));
                model.setId(resultSet.getInt("id"));
                model.setEnable(resultSet.getInt("enable"));
                model.setCreateTime(resultSet.getString("create_time"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user ";
        sql += getWhereSql(userModel);
        return JDBC.getJdbc().getCount(sql);
    }

    private String getWhereSql(UserModel userModel) {
        String sql = " where 1=1";
        if (!"".equals(userModel.getUsername())) {
            sql += "  and username like '%" + userModel.getUsername() + "%'";
        }
        if (!"".equals(userModel.getPhone())) {
            sql += " and phone like '%" + userModel.getPhone() + "%'";
        }
        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday <='" + userModel.getEnd() + "'";
        }
        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
            sql += " and birthday >='" + userModel.getStart() + "'";
        }
        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday  between '" + userModel.getStart() + "' and '" + userModel.getEnd() + "'";
        }
        if (!"".equals(userModel.getSex())) {
            sql += " and sex='" + userModel.getSex() + "'";

        }
        if (userModel.getEnable() != -1) {
            sql += " and enable=" + userModel.getEnable();
        }
        if (!"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            sql+=" and (";
            for (int i = 0; i < hobbys.length; i++) {
                if(i==0){
                    sql += "  find_in_set('" + hobbys[i] + "',`hobby`) ";
                }else {
                    sql += " or find_in_set('" + hobbys[i] + "',`hobby`) ";
                }
//                sql += " and find_in_set('" + hobbys[i] + "',`hobby`) ";
            }
            sql+=") ";
        }
        return sql;
    }

    @Override
    public int updateEnable(UserModel userModel) {
        return 0;
    }

    @Override
    public int enable(UserModel userModel) {
        return 0;
    }


    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.getJdbc().query(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int updateSex(UserModel userModel) {
        return 0;
    }

    @Override
    public int updatePassword(UserModel userModel) {
        return 0;
    }
}
